﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_InitDatabase]
#-- Purpose:		Sets the default configuration options
#--	Last Update:	06/08/2016
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	[dbo].[spMaint_InitDatabase]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_InitRAM]
AS

----------------------------------------------------
--- Set RAM reservation
---
SET NOCOUNT ON

DECLARE @Ram int, @RamNew int, @RamCurrent int, @Proc int, @blocks4GB int, @sCmd nvarchar(4000)
CREATE TABLE #SVer (
	ID INT,
	NAME SYSNAME,
	Internal_Value INT,
	Value NVARCHAR(512)
	)
CREATE TABLE #CurrentAlloc (
	[name] nvarchar(256) NOT NULL,
	[minimum] int NULL,
	[maximum] int NULL,
	[config_value] int NULL,
	[run_value] int NULL
)

INSERT #SVer EXEC master.dbo.xp_msver
EXEC sp_executesql N'sp_configure ''show advanced options'', 1; RECONFIGURE WITH OVERRIDE;'
INSERT INTO #CurrentAlloc EXEC sp_configure 'max server memory (MB)'

--- RAM calculation from:  https://www.sqlskills.com/blogs/jonathan/wow-an-online-calculator-to-misconfigure-your-sql-server-memory/
SELECT		@RamCurrent = [config_value] FROM #CurrentAlloc
SELECT 		@Proc = (SELECT Internal_Value FROM #SVer WHERE NAME = N'ProcessorCount'),
			@Ram =	(CASE	WHEN ISNULL((SELECT TOP 1 [cfg_max_server_RAM] FROM [dbo].[tblMaint_Config] WHERE [cfg_max_server_RAM] IS NOT NULL), (SELECT Internal_Value FROM #SVer WHERE NAME = N'PhysicalMemory')) <> (SELECT Internal_Value FROM #SVer WHERE NAME = N'PhysicalMemory')
								THEN ROUND((SELECT TOP 1 [cfg_max_server_RAM] FROM [dbo].[tblMaint_Config] WHERE [cfg_max_server_RAM] IS NOT NULL) / 1024.0, 0)
							ELSE ROUND((SELECT Internal_Value FROM #SVer WHERE NAME = N'PhysicalMemory') / 1024.0, 0)
							END),
			@blocks4GB = (@Ram / 4) + (CASE WHEN @Ram % 4 > 0 THEN 1 ELSE 0 END),
			@RamNew =	(@Ram - (
							1 + -- for O/S
							(CASE WHEN @blocks4GB >= 4 THEN 4 ELSE @blocks4GB END) + -- 1 GB for each 4 GB upto 16 GB (includes 1 extra for remainders)
							(CASE WHEN @blocks4GB > 4 THEN ((@blocks4GB - 4) / 2) ELSE 0 END) -- 1 GB for each 8 GB above 16 GB
						)) * 1024,
			@RamNew = (CASE WHEN @RamNew <= 0 THEN (@Ram * 1024) / 2 ELSE @RamNew END)
DROP TABLE #SVer
DROP TABLE #CurrentAlloc

IF @RamNew <> @RamCurrent
  BEGIN
	SELECT	@sCmd = N'sp_configure ''max server memory (MB)'', ' + CAST(@RamNew as varchar(1000)) + '; RECONFIGURE WITH OVERRIDE;'
	EXEC sp_executesql @sCmd
  END

EXEC sp_executesql N'sp_configure ''show advanced options'', 0; RECONFIGURE WITH OVERRIDE;'
SET NOCOUNT OFF
